package com.robot.merchant.dao;

import com.robot.merchant.pojo.Store;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * store数据访问接口
 *
 * @author wuyunbin
 */
public interface StoreDao extends JpaRepository<Store, String>, JpaSpecificationExecutor<Store> {
    /**
     * 通过phone查找store
     *
     * @param phone
     * @return
     */
    Store findByPhone(String phone);

    /**
     * (关键词搜索)根据storeName,recommendation,status的店铺
     *
     * @param storeName
     * @param recommendation
     * @param status
     * @return
     */
    @Query(value = "select * from merchant.store where store_name like %?% or recommendation like %?% and status=? and deleted_at is null ", nativeQuery = true)
    List<Store> findStoresByKeywordsAndStatusAndDeletedAtIsNull(String storeName, String recommendation, String status);

    /**
     * 根据halfGeoHash查找店铺
     *
     * @param halfGeoHash 半地理编码
     * @param status
     * @param pageable
     * @return
     */
    Page<Store> findStoresByHalfGeoHashAndStatusAndDeletedAtIsNull(String halfGeoHash, String status, Pageable pageable);

    /**
     * 根据geoHash查找店铺
     *
     * @param geoHash  全地理编码
     * @param status
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where geo_hash like ?% and status=? and deleted_at is null", nativeQuery = true)
    Page<Store> findStoresByGeoHash(String geoHash, String status, Pageable pageable);

    /**
     * 通过halfGeoHash查询店铺
     *
     * @param halfGeoHash
     * @return
     */
    List<Store> findByHalfGeoHash(String halfGeoHash);

    /**
     * 通过halfGeoHash和industryId查询店铺
     *
     * @param halfGeoHash
     * @param industryId
     * @return
     */
    List<Store> findByHalfGeoHashAndIndustryId(String halfGeoHash, String industryId);

    /**
     * 通过industryId查询店铺
     * @param industryId
     * @return
     */
    List<Store> findByIndustryIdAndDeletedAtIsNull(String industryId);
    /**
     * 通过id和companyId查询店铺
     *
     * @param id
     * @param companyId
     * @return
     */
    Store findByIdAndCompanyId(String id, String companyId);

    /**
     * 通过id和agentId查询店铺
     *
     * @param id
     * @param agentId
     * @return
     */
    Store findByIdAndAgentId(String id, String agentId);

    /**
     * 通过agentId查询店铺列表
     *
     * @param agentId
     * @param pageable
     * @return
     */
    Page<Store> findAllByAgentIdOrderByCreatedAtDesc(String agentId, Pageable pageable);

    /**
     * 根据agentId查找昨天开张的店铺
     *
     * @param agentId
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where agent_id=?  and TO_DAYS(NOW()) - TO_DAYS(created_at) =1", nativeQuery = true)
    Page<Store> findStoresByAgentIdAndYesterday(String agentId, Pageable pageable);

    /**
     * 根据agentId查找最近7日开张的店铺
     *
     * @param agentId
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where agent_id=?  and DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(created_at)", nativeQuery = true)
    Page<Store> findStoresByAgentIdAndWeek(String agentId, Pageable pageable);

    /**
     * 根据agentId查找总开张的店铺
     *
     * @param agentId
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where agent_id=?", nativeQuery = true)
    Page<Store> findStoresByAgentIdAndTotal(String agentId, Pageable pageable);

    /**
     * 根据id和status值获取店铺
     *
     * @param id
     * @param status
     * @return
     */
    Store findStoreByIdAndStatus(String id, String status);


    /**
     * 查找今日入驻商家
     *
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where TO_DAYS(NOW()) - TO_DAYS(created_at) =0", nativeQuery = true)
    Page<Store> findAllByToday(Pageable pageable);

    /**
     * 查找昨日入驻商家
     *
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where TO_DAYS(NOW()) - TO_DAYS(created_at) =1", nativeQuery = true)
    Page<Store> findAllByYesterday(Pageable pageable);

    /**
     * 查找最近7日入驻商家
     *
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(created_at)", nativeQuery = true)
    Page<Store> findAllByWeek(Pageable pageable);

    /**
     * 根据时间段查找总入驻商家
     *
     * @param startTime
     * @param endTime
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store where created_at between ? and ?", nativeQuery = true)
    Page<Store> findAllByBetweenAnd(String startTime, String endTime, Pageable pageable);

    /**
     * 查找总入驻商家
     *
     * @param pageable
     * @return
     */
    @Query(value = "select * from merchant.store", nativeQuery = true)
    Page<Store> findAllByTotal(Pageable pageable);
}
